package dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;


import objects.AccessPoint;
import objects.Measurement;
import objects.Point;
import objects.ReferencePoint;

public class ReferencePointsDAO {

	/**
	 * saves the reference points with theirs measurements in the database
	 * @param refPoints
	 * @param accessPoints
	 * @return
	 */
	public static int create(ArrayList<ReferencePoint> refPoints, HashMap<String, AccessPoint> accessPoints) {
		
		Connection con = null;
		int nbMeasurementsAdded = 0;
		
		try {
			Class.forName("org.hsqldb.jdbcDriver").newInstance();
			con = DriverManager.getConnection(DatabaseConnection.getDataServerUrl(), DatabaseConnection.getLogin(), DatabaseConnection.getPassword());
			con.setAutoCommit(false);

			// deletion of existing measurements and reference points
			CallableStatement cs = con.prepareCall("DELETE FROM MEASUREMENT");
			cs.executeUpdate();			
			cs = con.prepareCall("DELETE FROM REFERENCE_POINT");
			cs.executeUpdate();
			cs = con.prepareCall("ALTER SEQUENCE PUBLIC.REFPOINTS_SEQ RESTART WITH 0");
			cs.executeUpdate();
			
			CallableStatement cs1 = con.prepareCall("INSERT INTO REFERENCE_POINT (ID_REFERENCE_POINT, ABSCISSA, ORDINATE, HEIGHT)" +
					"VALUES (?, ?, ?, ?)");
			
			CallableStatement cs2 = con.prepareCall("INSERT INTO MEASUREMENT (ID_AP, ID_REFERENCE_POINT, POWER, FRIIS_INDEX)" +
					"VALUES (?, ?, ?, ?)");
			
			int i = 0;
			for (ReferencePoint  refPoint : refPoints) {
				cs1.setInt(1, i);
				cs1.setFloat(2, refPoint.getCoordinates().getX());
				cs1.setFloat(3, refPoint.getCoordinates().getY());
				cs1.setFloat(4, refPoint.getCoordinates().getZ());
				cs1.executeUpdate();
				
				if (refPoint.getMeasurements() == null) {
					int a =6;
					a++;
				}
				
				for (Measurement measurement : refPoint.getMeasurements().values()) {
					int id = accessPoints.get(measurement.getMacAdress()).getId();
					cs2.setInt(1, id);
					cs2.setInt(2, i);
					cs2.setFloat(3, measurement.getPowerValue());
					cs2.setFloat(4, measurement.getFriisIndex());
					nbMeasurementsAdded += cs2.executeUpdate();
				}
				
				i++;
			}
			
			con.commit();
		}
		catch (Exception e) {
			System.out.println("Failure : " + e.getMessage()); 
			System.exit(-1);
			try {
				con.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		}
		finally {
			if (con != null) {
				try {
					con.close();
				} 
				catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		
		return nbMeasurementsAdded;
	}
	
	/**
	 * 
	 * @return an arrayList of reference points
	 */
	public static ArrayList<ReferencePoint> read() {
		
		Connection con = null;
		ArrayList<ReferencePoint> refPoints = new ArrayList<ReferencePoint>();
		
		try {
			Class.forName("org.hsqldb.jdbcDriver").newInstance();
			con = DriverManager.getConnection(DatabaseConnection.getDataServerUrl(), DatabaseConnection.getLogin(), DatabaseConnection.getPassword());

			CallableStatement cs = con.prepareCall("SELECT RP.ID_REFERENCE_POINT, RP.ABSCISSA, RP.ORDINATE, RP.HEIGHT, AP.MAC, M.POWER, M.FRIIS_INDEX FROM REFERENCE_POINT RP " +
					"INNER JOIN MEASUREMENT M ON RP.ID_REFERENCE_POINT = M.ID_REFERENCE_POINT " +
					"INNER JOIN AP ON AP.ID_AP = M.ID_AP");
			
			ResultSet rs = cs.executeQuery();
			
			while (rs.next()) {
				int id = rs.getInt("ID_REFERENCE_POINT");
				ReferencePoint refPoint;
				
				if (refPoints.size() <= id) {
					refPoint = new ReferencePoint();
					refPoint.setId(id);
					refPoint.setCoordinates(new Point(rs.getFloat("ABSCISSA"), rs.getFloat("ORDINATE"), rs.getFloat("HEIGHT")));
					refPoint.setMeasurements(new HashMap<String, Measurement>());
				}
				else {
					refPoint = refPoints.get(id);
					refPoints.remove(id);
				}
				
				Measurement newMeasurement = new Measurement();
				newMeasurement.setMacAdress(rs.getString("MAC"));
				newMeasurement.setPowerValue(rs.getFloat("POWER"));
				newMeasurement.setFriisIndex(rs.getFloat("FRIIS_INDEX"));
				refPoint.getMeasurements().put(rs.getString("MAC"), newMeasurement);
				
				refPoints.add(refPoint);
			}
		}
		catch (Exception e) {
			System.out.println("Failure : " + e.getMessage()); 
			System.exit(-1);
		}
		finally {
			if (con != null) {
				try {
					con.close();
				} 
				catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		
		return refPoints;
	}
}
